# -*- coding: utf-8 -*-
"""
Spyder 编辑器

这是一个临时脚本文件。
"""
import pandas as pd
from sqlalchemy import create_engine
conn=create_engine('mysql+pymysql://root:123456@localhost:3306/kkf?charset=utf8')
df=pd.read_sql("select * from tbl_course",conn)
df=df[df['open_time']<'2019-12']
df=df.drop('dependency',axis=1).join(df['dependency'].str.split('/',expand=True).stack().reset_index(level=1,drop=True).rename('dependency'))
df=df.drop('dependency',axis=1).join(df['dependency'].str.split('-',expand=True).stack().reset_index(level=1,drop=True).rename('dependency'))
df=df.drop_duplicates()
df['hours_per_week']=df['hours_per_week'].astype(int)
df1=df['hours_per_week'].groupby(df['dependency']).sum()
df2=df['lecturer_id'].groupby(df['dependency']).nunique()
df=pd.merge(df1,df2,how="inner",left_on='dependency',right_on='dependency')
df['dependency'] = df.index
df.columns=['total_hours','uniqs','dependency']
data=df
data=data[['dependency','total_hours','uniqs']]
df=data.sort_values('total_hours',ascending=False)
df.to_csv(r'C:\Users\PC\Desktop\源码文件\out2.csv',index=False)
